{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5250aed9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据集维度: (395, 33)\n",
      "前5行数据:\n",
      "  school sex  age address famsize Pstatus  Medu  Fedu     Mjob      Fjob  ...  \\\n",
      "0     GP   F   18       U     GT3       A     4     4  at_home   teacher  ...   \n",
      "1     GP   F   17       U     GT3       T     1     1  at_home     other  ...   \n",
      "2     GP   F   15       U     LE3       T     1     1  at_home     other  ...   \n",
      "3     GP   F   15       U     GT3       T     4     2   health  services  ...   \n",
      "4     GP   F   16       U     GT3       T     3     3    other     other  ...   \n",
      "\n",
      "  famrel freetime  goout  Dalc  Walc health absences  G1  G2  G3  \n",
      "0      4        3      4     1     1      3        6   5   6   6  \n",
      "1      5        3      3     1     1      3        4   5   5   6  \n",
      "2      4        3      2     2     3      3       10   7   8  10  \n",
      "3      3        2      2     1     1      5        2  15  14  15  \n",
      "4      4        3      2     1     2      5        4   6  10  10  \n",
      "\n",
      "[5 rows x 33 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 数学科目数据集URL\n",
    "math_url = \"https://archive.ics.uci.edu/ml/machine-learning-databases/00320/student.zip\"\n",
    "\n",
    "# 自动下载并解压\n",
    "def download_uci_student_data():\n",
    "    import io\n",
    "    import requests\n",
    "    from zipfile import ZipFile\n",
    "    \n",
    "    # 发送请求获取数据\n",
    "    r = requests.get(math_url)\n",
    "    z = ZipFile(io.BytesIO(r.content))\n",
    "    \n",
    "    # 提取数学科目数据\n",
    "    math_data = pd.read_csv(z.open('student-mat.csv'), sep=';')\n",
    "    \n",
    "    return math_data\n",
    "\n",
    "# 调用函数获取数据\n",
    "student_data = download_uci_student_data()\n",
    "print(f\"数据集维度: {student_data.shape}\")\n",
    "print(f\"前5行数据:\\n{student_data.head()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8ef253f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "77f9addb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据集维度: (395, 1)\n",
      "列名列表: ['school;sex;age;address;famsize;Pstatus;Medu;Fedu;Mjob;Fjob;reason;guardian;traveltime;studytime;failures;schoolsup;famsup;paid;activities;nursery;higher;internet;romantic;famrel;freetime;goout;Dalc;Walc;health;absences;G1;G2;G3']\n",
      "前5行数据:\n",
      "   school;sex;age;address;famsize;Pstatus;Medu;Fedu;Mjob;Fjob;reason;guardian;traveltime;studytime;failures;schoolsup;famsup;paid;activities;nursery;higher;internet;romantic;famrel;freetime;goout;Dalc;Walc;health;absences;G1;G2;G3\n",
      "0  GP;\"F\";18;\"U\";\"GT3\";\"A\";4;4;\"at_home\";\"teacher...                                                                                                                                                                                 \n",
      "1  GP;\"F\";17;\"U\";\"GT3\";\"T\";1;1;\"at_home\";\"other\";...                                                                                                                                                                                 \n",
      "2  GP;\"F\";15;\"U\";\"LE3\";\"T\";1;1;\"at_home\";\"other\";...                                                                                                                                                                                 \n",
      "3  GP;\"F\";15;\"U\";\"GT3\";\"T\";4;2;\"health\";\"services...                                                                                                                                                                                 \n",
      "4  GP;\"F\";16;\"U\";\"GT3\";\"T\";3;3;\"other\";\"other\";\"h...                                                                                                                                                                                 \n",
      "\n",
      "数据类型:\n",
      " school;sex;age;address;famsize;Pstatus;Medu;Fedu;Mjob;Fjob;reason;guardian;traveltime;studytime;failures;schoolsup;famsup;paid;activities;nursery;higher;internet;romantic;famrel;freetime;goout;Dalc;Walc;health;absences;G1;G2;G3    object\n",
      "dtype: object\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<>:12: SyntaxWarning: invalid escape sequence '\\s'\n",
      "<>:12: SyntaxWarning: invalid escape sequence '\\s'\n",
      "C:\\Users\\sfex0713\\AppData\\Local\\Temp\\ipykernel_13208\\1133363880.py:12: SyntaxWarning: invalid escape sequence '\\s'\n",
      "  data = pd.read_csv(\"C:\\\\Users\\sfex0713\\Desktop\\python数据项目分析\\student\\student-mat.csv\")\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.metrics import mean_squared_error, r2_score\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from statsmodels.stats.outliers_influence import variance_inflation_factor\n",
    "\n",
    "# 1. 数据加载与初步探索\n",
    "data = pd.read_csv(\"C:\\\\Users\\sfex0713\\Desktop\\python数据项目分析\\student\\student-mat.csv\")\n",
    "print(f\"数据集维度: {data.shape}\")\n",
    "print(\"列名列表:\", data.columns.tolist())  # 打印所有列名\n",
    "print(\"前5行数据:\\n\", data.head())\n",
    "print(\"\\n数据类型:\\n\", data.dtypes)\n",
    "\n",
    "# 2. 数据预处理 - 先处理异常值再重命名成绩列\n",
    "# 处理分类变量（二值变量映射）\n",
    "binary_cols = ['school', 'sex', 'address', 'famsize', 'Pstatus', 'schoolsup', \n",
    "               'famsup', 'paid', 'activities', 'nursery', 'higher', 'internet', 'romantic']\n",
    "binary_mapping = {'yes': 1, 'no': 0, 'GP': 0, 'MS': 1, 'F': 0, 'M': 1, \n",
    "                  'U': 1, 'R': 0, 'GT3': 1, 'LE3': 0, 'A': 0, 'T': 1}\n",
    "\n",
    "for col in binary_cols:\n",
    "    if col in data.columns:\n",
    "        data[col] = data[col].map(binary_mapping).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "229a29bf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "将处理以下数值列的异常值: []\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<Figure size 1500x2000 with 0 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "异常值处理报告:\n",
      "Empty DataFrame\n",
      "Columns: [异常值数量, 占比]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "# 3. 异常值检测与处理\n",
    "def detect_and_treat_outliers(df, column, threshold=1.5):\n",
    "    \"\"\"使用IQR方法检测和处理异常值\"\"\"\n",
    "    if column not in df.columns:\n",
    "        print(f\"警告: 列 '{column}' 不存在，跳过处理\")\n",
    "        return df, pd.DataFrame()\n",
    "    \n",
    "    Q1 = df[column].quantile(0.25)\n",
    "    Q3 = df[column].quantile(0.75)\n",
    "    IQR = Q3 - Q1\n",
    "    \n",
    "    if IQR == 0:  # 避免除零错误\n",
    "        print(f\"警告: 列 '{column}' 的IQR为0，跳过处理\")\n",
    "        return df, pd.DataFrame()\n",
    "    \n",
    "    lower_bound = Q1 - threshold * IQR\n",
    "    upper_bound = Q3 + threshold * IQR\n",
    "    \n",
    "    # 识别异常值\n",
    "    outliers = df[(df[column] < lower_bound) | (df[column] > upper_bound)]\n",
    "    print(f\"\\n{column}异常值统计: {len(outliers)}个 ({len(outliers)/len(df):.2%})\")\n",
    "    \n",
    "    # 盖帽法处理异常值\n",
    "    df[column] = np.where(df[column] < lower_bound, lower_bound, df[column])\n",
    "    df[column] = np.where(df[column] > upper_bound, upper_bound, df[column])\n",
    "    \n",
    "    return df, outliers\n",
    "\n",
    "# 需要处理异常值的数值型特征（使用原始列名）\n",
    "numeric_cols = [\n",
    "    'age', 'Medu', 'Fedu', 'traveltime', 'studytime', \n",
    "    'failures', 'famrel', 'freetime', 'goout', 'Dalc', \n",
    "    'Walc', 'health', 'absences', 'G1', 'G2', 'G3'\n",
    "]\n",
    "\n",
    "# 检查并只保留实际存在的列\n",
    "existing_numeric_cols = [col for col in numeric_cols if col in data.columns]\n",
    "print(\"\\n将处理以下数值列的异常值:\", existing_numeric_cols)\n",
    "\n",
    "# 可视化异常值分布\n",
    "plt.figure(figsize=(15, 20))\n",
    "for i, col in enumerate(existing_numeric_cols, 1):\n",
    "    plt.subplot(5, 4, i)\n",
    "    sns.boxplot(y=data[col])\n",
    "    plt.title(f'{col}异常值检测', fontsize=10)\n",
    "plt.tight_layout()\n",
    "plt.savefig('outlier_detection_before.png', dpi=300)\n",
    "plt.show()\n",
    "\n",
    "# 处理异常值并记录处理情况\n",
    "outliers_report = {}\n",
    "for col in existing_numeric_cols:\n",
    "    data, outliers = detect_and_treat_outliers(data, col)\n",
    "    outliers_report[col] = len(outliers)\n",
    "\n",
    "# 创建异常值报告\n",
    "outliers_df = pd.DataFrame.from_dict(outliers_report, orient='index', columns=['异常值数量'])\n",
    "outliers_df['占比'] = outliers_df['异常值数量'] / len(data)\n",
    "print(\"\\n异常值处理报告:\")\n",
    "print(outliers_df.sort_values('异常值数量', ascending=False))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
